package com.gs.synedocket.db.sqlite;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import androidx.annotation.Nullable;

import com.gs.synedocket.db.bean.AddColumnBean;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends android.database.sqlite.SQLiteOpenHelper {
    private static final String CREATE_TABLE_START_SQL = "CREATE TABLE IF NOT EXISTS ";
    private static final String CREATE_INDEX_START_SQL = "CREATE INDEX IF NOT EXISTS ";
    private static final String CREATE_TABLE_PRIMARY_SQL = " integer primary key autoincrement,";
    private static final String ALTER_TABLE_START_SQL = "ALTER TABLE ";
    private static final String INSERT_TABLE_START_SQL = "INSERT INTO ";
    private static final String DB_NAME = "synEDocket";
    public static final String SYS_TABLE_SEQUENCE = "sqlite_sequence";
    public static final String TABLE_ADMIXTURE = "mst_Admixture";
    public static final String TABLE_CEMENT = "mst_Cement";
    public static final String TABLE_CUSTOMER = "mst_Customer";
    public static final String TABLE_MIX = "mst_Mix";
    public static final String TABLE_PLANT = "mst_Plant";
    public static final String TABLE_PRODUCT = "mst_Product";
    public static final String TABLE_SITE = "mst_Site";
    public static final String TABLE_TEAM = "mst_Team";
    public static final String TABLE_TRUCK = "mst_Truck";
    public static final String TABLE_USER_TRUCK = "mst_UserTruck";
    public static final String TABLE_DOCKET = "tim_Docket";
    public static final String TABLE_DOCKET_ACC_REP = "tim_DocketAccRep";
    public static final String TABLE_DOCKET_ACC_REP_PHOTO = "tim_DocketAccRepPhoto";
    public static final String TABLE_DOCKET_ADD_CHARGE = "tim_DocketAddCharge";
    public static final String TABLE_DOCKET_ADDITIONAL = "tim_DocketAdditional";
    public static final String TABLE_DOCKET_ADMIXTURE = "tim_DocketAdmixture";
    public static final String TABLE_DOCKET_PHOTO = "tim_DocketPhoto";
    public static final String TABLE_DOCKET_QC = "tim_DocketQc";
    public static final String TABLE_PARAM_TYPE = "sys_ParmsType";
    public static final String TABLE_MENU = "sys_Menu";
    private static final int VERSION = 5;

    public DBHelper(@Nullable Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        StringBuilder admixtureSql = new StringBuilder();
        admixtureSql.append(CREATE_TABLE_START_SQL).append(TABLE_ADMIXTURE).append(" ( ");
        admixtureSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        admixtureSql.append(" id").append(" varchar(100),");
        admixtureSql.append(" code").append(" varchar(50) ,");
        admixtureSql.append(" enName").append(" varchar(50) ,");
        admixtureSql.append(" cnName").append(" varchar(50) ,");
        admixtureSql.append(" state").append(" INTEGER ,");
        admixtureSql.append(" upload").append(" INTEGER )");
        StringBuilder cementSql = new StringBuilder();
        cementSql.append(CREATE_TABLE_START_SQL).append(TABLE_CEMENT).append(" ( ");
        cementSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        cementSql.append(" id").append(" varchar(100),");
        cementSql.append(" code").append(" varchar(50) ,");
        cementSql.append(" enName").append(" varchar(50) ,");
        cementSql.append(" cnName").append(" varchar(50) ,");
        cementSql.append(" state").append(" smallint ,");
        cementSql.append(" upload").append(" INTEGER )");
        StringBuilder customerSql = new StringBuilder();
        customerSql.append(CREATE_TABLE_START_SQL).append(TABLE_CUSTOMER).append(" ( ");
        customerSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        customerSql.append(" id").append(" varchar(100),");
        customerSql.append(" code").append(" varchar(100) ,");
        customerSql.append(" enName").append(" varchar(100) ,");
        customerSql.append(" cnName").append(" varchar(100) ,");
        customerSql.append(" contacter").append(" varchar(100) ,");
        customerSql.append(" tel").append(" varchar(50) ,");
        customerSql.append(" state").append(" INTEGER ,");
        customerSql.append(" flag").append(" INTEGER ,");
        customerSql.append(" upload").append(" INTEGER )");
        StringBuilder mixSql = new StringBuilder();
        mixSql.append(CREATE_TABLE_START_SQL).append(TABLE_MIX).append(" ( ");
        mixSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        mixSql.append(" id").append(" varchar(100),");
        mixSql.append(" code").append(" varchar(50) ,");
        mixSql.append(" enName").append(" varchar(50) ,");
        mixSql.append(" cnName").append(" varchar(50) ,");
        mixSql.append(" state").append(" INTEGER ,");
        mixSql.append(" upload").append(" INTEGER )");
        StringBuilder plantSql = new StringBuilder();
        plantSql.append(CREATE_TABLE_START_SQL).append(TABLE_PLANT).append(" ( ");
        plantSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        plantSql.append(" id").append(" varchar(100),");
        plantSql.append(" code").append(" varchar(50) ,");
        plantSql.append(" enName").append(" varchar(50) ,");
        plantSql.append(" cnName").append(" varchar(50) ,");
        plantSql.append(" state").append(" INTEGER ,");
        plantSql.append(" upload").append(" INTEGER )");
        StringBuilder productSql = new StringBuilder();
        productSql.append(CREATE_TABLE_START_SQL).append(TABLE_PRODUCT).append(" ( ");
        productSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        productSql.append(" id").append(" varchar(100),");
        productSql.append(" code").append(" varchar(50) ,");
        productSql.append(" enName").append(" varchar(50) ,");
        productSql.append(" cnName").append(" varchar(50) ,");
        productSql.append(" state").append(" INTEGER ,");
        productSql.append(" upload").append(" INTEGER )");
        StringBuilder siteSql = new StringBuilder();
        siteSql.append(CREATE_TABLE_START_SQL).append(TABLE_SITE).append(" ( ");
        siteSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        siteSql.append(" id").append(" varchar(100),");
        siteSql.append(" code").append(" varchar(255) ,");
        siteSql.append(" enName").append(" varchar(255) ,");
        siteSql.append(" cnName").append(" varchar(255) ,");
        siteSql.append(" emails").append(" varchar(255) ,");
        siteSql.append(" gps").append(" varchar(255) ,");
        siteSql.append(" state").append(" INTEGER ,");
        siteSql.append(" upload").append(" INTEGER )");
        StringBuilder teamSql = new StringBuilder();
        teamSql.append(CREATE_TABLE_START_SQL).append(TABLE_TEAM).append(" ( ");
        teamSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        teamSql.append(" id").append(" varchar(100),");
        teamSql.append(" code").append(" varchar(50) ,");
        teamSql.append(" enName").append(" varchar(50) ,");
        teamSql.append(" cnName").append(" varchar(50) ,");
        teamSql.append(" state").append(" INTEGER ,");
        teamSql.append(" upload").append(" INTEGER )");
        StringBuilder truckSql = new StringBuilder();
        truckSql.append(CREATE_TABLE_START_SQL).append(TABLE_TRUCK).append(" ( ");
        truckSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        truckSql.append(" id").append(" varchar(100),");
        truckSql.append(" code").append(" varchar(50) ,");
        truckSql.append(" enName").append(" varchar(50) ,");
        truckSql.append(" cnName").append(" varchar(50) ,");
        truckSql.append(" state").append(" INTEGER ,");
        truckSql.append(" upload").append(" INTEGER )");
        StringBuilder userTruckSql = new StringBuilder();
        userTruckSql.append(CREATE_TABLE_START_SQL).append(TABLE_USER_TRUCK).append(" ( ");
        userTruckSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        userTruckSql.append(" id").append(" varchar(100),");
        userTruckSql.append(" userId").append(" varchar(100),");
        userTruckSql.append(" truckId").append(" varchar(100),");
        userTruckSql.append(" def").append(" INTEGER,");
        userTruckSql.append(" upload").append(" INTEGER )");
        StringBuilder accRepPhotoSql = new StringBuilder();
        accRepPhotoSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_ACC_REP_PHOTO).append(" ( ");
        accRepPhotoSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        accRepPhotoSql.append(" id").append(" varchar(100),");
        accRepPhotoSql.append(" accRepId").append(" varchar(100),");
        accRepPhotoSql.append(" photo").append(" varchar(255) ,");
        accRepPhotoSql.append(" upload").append(" INTEGER )");
        StringBuilder addChargeSql = new StringBuilder();
        addChargeSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_ADD_CHARGE).append(" ( ");
        addChargeSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        addChargeSql.append(" id").append(" varchar(100),");
        addChargeSql.append(" docketId").append(" varchar(100),");
        addChargeSql.append(" acceptedQty").append(" decimal(13, 2) ,");
        addChargeSql.append(" rejectQty").append(" decimal(13, 2) ,");
        addChargeSql.append(" rejectReason").append(" smallint ,");
        addChargeSql.append(" rejectRemark").append(" varchar(255) ,");
        addChargeSql.append(" rejectVal").append(" decimal(13, 2) ,");
        addChargeSql.append(" returnQty").append(" decimal(13, 2) ,");
        addChargeSql.append(" payDumpCharge").append(" INTEGER ,");
        addChargeSql.append(" payMultipleDrop").append(" INTEGER ,");
        addChargeSql.append(" payOther").append(" INTEGER ,");
        addChargeSql.append(" payOtherRemark").append(" TEXT ,");
        addChargeSql.append(" mileage").append(" INTEGER ,");
        addChargeSql.append(" takeSample").append(" INTEGER ,");
        addChargeSql.append(" photo").append(" varchar(250) ,");
        addChargeSql.append(" photo1").append(" varchar(250) ,");
        addChargeSql.append(" state").append(" smallint ,");
        addChargeSql.append(" upload").append(" INTEGER )");
        StringBuilder additionalSql = new StringBuilder();
        additionalSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_ADDITIONAL).append(" ( ");
        additionalSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        additionalSql.append(" id").append(" varchar(100),");
        additionalSql.append(" docketId").append(" varchar(100),");
        additionalSql.append(" slump").append(" decimal(13, 2) ,");
        additionalSql.append(" temperature").append(" decimal(13, 2) ,");
        additionalSql.append(" takeSample").append(" INTEGER ,");
        additionalSql.append(" addMileage").append(" INTEGER ,");
        additionalSql.append(" addType").append(" smallint ,");
        additionalSql.append(" addRemark").append(" varchar(50) ,");
        additionalSql.append(" gps").append(" varchar(100) ,");
        additionalSql.append(" gpsLoc").append(" varchar(100) ,");
        additionalSql.append(" upload").append(" INTEGER )");
        StringBuilder docketAdmixtureSql = new StringBuilder();
        docketAdmixtureSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_ADMIXTURE).append(" ( ");
        docketAdmixtureSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        docketAdmixtureSql.append(" id").append(" varchar(100),");
        docketAdmixtureSql.append(" docketId").append(" varchar(100),");
        docketAdmixtureSql.append(" admixtureId").append(" varchar(100),");
        docketAdmixtureSql.append(" upload").append(" INTEGER )");
        StringBuilder photoSql = new StringBuilder();
        photoSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_PHOTO).append(" ( ");
        photoSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        photoSql.append(" id").append(" varchar(100),");
        photoSql.append(" docketId").append(" varchar(100),");
        photoSql.append(" photo").append(" varchar(255) ,");
        photoSql.append(" upload").append(" INTEGER )");
        StringBuilder qcSql = new StringBuilder();
        qcSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_QC).append(" ( ");
        qcSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        qcSql.append(" id").append(" varchar(100),");
        qcSql.append(" docketId").append(" varchar(100),");
        qcSql.append(" rejectReason").append(" smallint ,");
        qcSql.append(" rejectVal").append(" decimal(13, 2) ,");
        qcSql.append(" rejectRemak").append(" varchar(250) ,");
        qcSql.append(" photo").append(" varchar(255) ,");
        qcSql.append(" photo1").append(" varchar(255) ,");
        qcSql.append(" state").append(" smallint DEFAULT -1,");
        qcSql.append(" gps").append(" varchar(100) ,");
        qcSql.append(" gpsLoc").append(" varchar(100) ,");
        qcSql.append(" upload").append(" INTEGER )");
        StringBuilder paramSql = new StringBuilder();
        paramSql.append(CREATE_TABLE_START_SQL).append(TABLE_PARAM_TYPE).append(" ( ");
        paramSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        paramSql.append(" id").append(" varchar(100),");
        paramSql.append(" code").append(" varchar(100) ,");
        paramSql.append(" enName").append(" varchar(100) ,");
        paramSql.append(" cnName").append(" varchar(100) ,");
        paramSql.append(" typeName").append(" varchar(100) ,");
        paramSql.append(" orderId").append(" INTEGER ,");
        paramSql.append(" flag").append(" INTEGER ,");
        paramSql.append(" icon").append(" varchar(100) ,");
        paramSql.append(" upload").append(" INTEGER )");
        StringBuilder menuSql = new StringBuilder();
        menuSql.append(CREATE_TABLE_START_SQL).append(TABLE_MENU).append(" ( ");
        menuSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        menuSql.append(" id").append(" varchar(100),");
        menuSql.append(" pId").append(" varchar(100) ,");
        menuSql.append(" enName").append(" varchar(50) ,");
        menuSql.append(" cnName").append(" varchar(50) ,");
        menuSql.append(" typeName").append(" varchar(50) ,");
        menuSql.append(" appAct").append(" varchar(50) ,");
        menuSql.append(" appIcon").append(" varchar(50) ,");
        menuSql.append(" color").append(" varchar(20) ,");
        menuSql.append(" viewFlag").append(" varchar(50) ,");
        menuSql.append(" quickFlag").append(" varchar(50) ,");
        menuSql.append(" powerFlag").append(" INTEGER ,");
        menuSql.append(" state").append(" INTEGER ,");
        menuSql.append(" operFlag").append(" INTEGER ,");
        menuSql.append(" orderId").append(" INTEGER ,");
        menuSql.append(" gpFlag").append(" INTEGER ,");
        menuSql.append(" hasChildren").append(" INTEGER ,");
        menuSql.append(" upload").append(" INTEGER )");
        try {
            db.execSQL(admixtureSql.toString());
            db.execSQL(cementSql.toString());
            db.execSQL(customerSql.toString());
            db.execSQL(mixSql.toString());
            db.execSQL(plantSql.toString());
            db.execSQL(productSql.toString());
            db.execSQL(siteSql.toString());
            db.execSQL(teamSql.toString());
            db.execSQL(truckSql.toString());
            db.execSQL(userTruckSql.toString());
            db.execSQL(createDocketSql());
            db.execSQL(createAccRepSql());
            db.execSQL(accRepPhotoSql.toString());
            db.execSQL(addChargeSql.toString());
            db.execSQL(additionalSql.toString());
            db.execSQL(docketAdmixtureSql.toString());
            db.execSQL(photoSql.toString());
            db.execSQL(qcSql.toString());
            db.execSQL(paramSql.toString());
            db.execSQL(menuSql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String createAccRepSql() {
        StringBuilder accRepSql = new StringBuilder();
        accRepSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET_ACC_REP).append(" ( ");
        accRepSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        accRepSql.append(" id").append(" varchar(100),");
        accRepSql.append(" docketId").append(" varchar(100),");
        accRepSql.append(" accRepType").append(" INTEGER ,");
        accRepSql.append(" accRepAt").append(" DATETIME ,");
        accRepSql.append(" detail").append(" varchar(250) ,");
        accRepSql.append(" gps").append(" varchar(100) ,");
        accRepSql.append(" gpsLoc").append(" varchar(100) ,");
        accRepSql.append(" upload").append(" INTEGER )");
        return accRepSql.toString();
    }

    private String createDocketSql() {
        StringBuilder docketSql = new StringBuilder();
        docketSql.append(CREATE_TABLE_START_SQL).append(TABLE_DOCKET).append(" ( ");
        docketSql.append(" _id").append(CREATE_TABLE_PRIMARY_SQL);
        docketSql.append(" id").append(" varchar(100),");
        docketSql.append(" docketNo").append(" varchar(50) ,");
        docketSql.append(" docketAt").append(" DATETIME ,");
        docketSql.append(" contractNo").append(" varchar(10) ,");
        docketSql.append(" salesOrder").append(" varchar(50) ,");
        docketSql.append(" additionalCharge1").append(" varchar(255) ,");
        docketSql.append(" additionalCharge2").append(" varchar(255) ,");
        docketSql.append(" siteId").append(" varchar(100) ,");
        docketSql.append(" truckNo").append(" varchar(20) ,");
        docketSql.append(" preTruckNo").append(" varchar(50) ,");
        docketSql.append(" plantId").append(" varchar(100) ,");
        docketSql.append(" customerId").append(" varchar(100) ,");
        docketSql.append(" mixId").append(" varchar(100) ,");
        docketSql.append(" productId").append(" varchar(100) ,");
        docketSql.append(" teamId").append(" varchar(100) ,");
        docketSql.append(" cementId").append(" varchar(100) ,");
        docketSql.append(" batchTime").append(" DATETIME ,");
        docketSql.append(" distance").append(" decimal(13, 2) ,");
        docketSql.append(" loadQty").append(" decimal(13, 2) ,");
        docketSql.append(" cumQty").append(" decimal(13, 2) ,");
        docketSql.append(" orderQty").append(" decimal(13, 2) ,");
        docketSql.append(" lastDriverTel").append(" varchar(50) ,");
        docketSql.append(" specialTerm").append(" TEXT ,");
        docketSql.append(" additionalTerm").append(" TEXT ,");
        docketSql.append(" kWahQRCode").append(" TEXT ,");
        docketSql.append(" custQRCode").append(" TEXT ,");
        docketSql.append(" authorityQRCode").append(" TEXT ,");
        docketSql.append(" authorityName").append(" TEXT ,");
        docketSql.append(" nominalMaxAgg").append(" varchar(255) ,");
        docketSql.append(" custPo").append(" varchar(50) ,");
        docketSql.append(" slump").append(" decimal(13, 2) ,");
        docketSql.append(" slump1").append(" decimal(13, 2) ,");
        docketSql.append(" slump2").append(" decimal(13, 2) ,");
        docketSql.append(" temperature").append(" decimal(13, 2) ,");
        docketSql.append(" signPhoto").append(" varchar(255) ,");
        docketSql.append(" scanPhoto").append(" varchar(255) ,");
        docketSql.append(" arriveSiteAt").append(" DATETIME ,");
        docketSql.append(" arriveSiteGps").append(" varchar(255) ,");
        docketSql.append(" arriveSiteGpsLoc").append(" varchar(255) ,");
        docketSql.append(" startDisAt").append(" DATETIME ,");
        docketSql.append(" startDisGps").append(" varchar(255) ,");
        docketSql.append(" startDisGpsLoc").append(" varchar(255) ,");
        docketSql.append(" endDisAt").append(" DATETIME ,");
        docketSql.append(" endDisGps").append(" varchar(255) ,");
        docketSql.append(" endDisGpsLoc").append(" varchar(255) ,");
        docketSql.append(" leaveSiteAt").append(" DATETIME ,");
        docketSql.append(" leaveSiteGps").append(" varchar(255) ,");
        docketSql.append(" leaveSiteGpsLoc").append(" varchar(255) ,");
        docketSql.append(" arrivePlantAt").append(" DATETIME ,");
        docketSql.append(" arrivePlantGps").append(" varchar(255) ,");
        docketSql.append(" arrivePlantGpsLoc").append(" varchar(255) ,");
        docketSql.append(" contactTel").append(" varchar(50) ,");
        docketSql.append(" contactPerson").append(" varchar(50) ,");
        docketSql.append(" truckState").append(" smallint ,");
        docketSql.append(" state").append(" smallint ,");
        docketSql.append(" remark").append(" varchar(50) ,");
        docketSql.append(" driverRemark").append(" TEXT ,");
        docketSql.append(" changeTruck").append(" nvarchar(10) ,");
        docketSql.append(" extraAddress").append(" nvarchar(800) ,");
        docketSql.append(" siteCode").append(" nvarchar(100) ,");
        docketSql.append(" siteName").append(" nvarchar(800) ,");
        docketSql.append(" siteChineseName").append(" nvarchar(800) ,");
        docketSql.append(" siteGPS").append(" nvarchar(100) ,");
        docketSql.append(" customerCode").append(" nvarchar(50) ,");
        docketSql.append(" custName").append(" nvarchar(800) ,");
        docketSql.append(" custChineseName").append(" nvarchar(800) ,");
        docketSql.append(" productCode").append(" nvarchar(50) ,");
        docketSql.append(" productName").append(" nvarchar(800) ,");
        docketSql.append(" productChineseName").append(" nvarchar(800) ,");
        docketSql.append(" createdAt").append(" DATETIME ,");
        docketSql.append(" modifyAt").append(" DATETIME ,");
        docketSql.append(" upload").append(" INTEGER )");
        return docketSql.toString();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            if (oldVersion < 2) {
                changeDateTime(db);
            }
            addModifyAtDocket(db);
            if (oldVersion < 4) {
                addChangeTruckDocket(db);
            }
            if (oldVersion < 5) {
                addSiteCusProDocket(db);
            }
        }
    }

    private void addSiteCusProDocket(SQLiteDatabase db) {
        if (checkColumnNotExist(db, TABLE_DOCKET, "siteCode")) {
            List<AddColumnBean> list = new ArrayList<>();
            list.add(new AddColumnBean("siteCode", "nvarchar(100)"));
            list.add(new AddColumnBean("siteName", "nvarchar(800)"));
            list.add(new AddColumnBean("siteChineseName", "nvarchar(800)"));
            list.add(new AddColumnBean("siteGPS", "nvarchar(100)"));
            list.add(new AddColumnBean("customerCode", "nvarchar(50)"));
            list.add(new AddColumnBean("custName", "nvarchar(800)"));
            list.add(new AddColumnBean("custChineseName", "nvarchar(800)"));
            list.add(new AddColumnBean("productCode", "nvarchar(50)"));
            list.add(new AddColumnBean("productName", "nvarchar(800)"));
            list.add(new AddColumnBean("productChineseName", "nvarchar(800)"));
            addColumn(db, TABLE_DOCKET, list);
        }
    }

    private void addChangeTruckDocket(SQLiteDatabase db) {
        if (checkColumnNotExist(db, TABLE_DOCKET, "changeTruck")) {
            List<AddColumnBean> list = new ArrayList<>();
            list.add(new AddColumnBean("changeTruck", "nvarchar(10)"));
            list.add(new AddColumnBean("extraAddress", "nvarchar(800)"));
            addColumn(db, TABLE_DOCKET, list);
        }
    }

    private void addColumn(SQLiteDatabase db, String table, List<AddColumnBean> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append(ALTER_TABLE_START_SQL).append(table)
                .append(" ADD ");
        for (AddColumnBean column : columns) {
            StringBuilder addSql = new StringBuilder();
            addSql.append(sql).append(" ").append(column.getColumn()).append(" ").append(column.getType());
            try {
                db.execSQL(addSql.toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void addModifyAtDocket(SQLiteDatabase db) {
        if (checkColumnNotExist(db, TABLE_DOCKET, "modifyAt")) {
            List<AddColumnBean> list = new ArrayList<>();
            list.add(new AddColumnBean("modifyAt", "DATETIME"));
            addColumn(db, TABLE_DOCKET, list);
        }
    }

    private static final String TABLE_DOCKET_OLD = "tim_Docket_old_20220326";
    private static final String TABLE_DOCKET_ACC_REP_OLD = "tim_DocketAccRep_old_20220326";

    private void changeDateTime(SQLiteDatabase db) {
        if (db == null || db.isReadOnly()) {
            db = getWritableDatabase();
        }
        StringBuilder alertDocketSql = new StringBuilder();
        alertDocketSql.append(ALTER_TABLE_START_SQL)
                .append(TABLE_DOCKET)
                .append(" RENAME TO ")
                .append(TABLE_DOCKET_OLD);
        StringBuilder insertSql = new StringBuilder();
        insertSql.append(INSERT_TABLE_START_SQL)
                .append(TABLE_DOCKET)
                .append(" SELECT *,batchTime FROM ")
                .append(TABLE_DOCKET_OLD);
        StringBuilder updateSql = new StringBuilder();
        updateSql.append("UPDATE ")
                .append(TABLE_DOCKET)
                .append(" SET createdAt = IFNULL(batchTime,docketAt) WHERE id=id");
        StringBuilder deleteSql = new StringBuilder();
        deleteSql.append("DROP TABLE ")
                .append(TABLE_DOCKET_OLD);
        try {
            db.execSQL(alertDocketSql.toString());
            db.execSQL(createDocketSql());
            db.execSQL(insertSql.toString());
            db.execSQL(updateSql.toString());
            db.execSQL(deleteSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        StringBuilder alertAccRepSql = new StringBuilder();
        alertAccRepSql.append(ALTER_TABLE_START_SQL)
                .append(TABLE_DOCKET_ACC_REP)
                .append(" RENAME TO ")
                .append(TABLE_DOCKET_ACC_REP_OLD);
        StringBuilder insertAccRepSql = new StringBuilder();
        insertAccRepSql.append(INSERT_TABLE_START_SQL)
                .append(TABLE_DOCKET_ACC_REP)
                .append(" SELECT * FROM ")
                .append(TABLE_DOCKET_ACC_REP_OLD);
        StringBuilder deleteAccRepSql = new StringBuilder();
        deleteAccRepSql.append("DROP TABLE ")
                .append(TABLE_DOCKET_ACC_REP_OLD);
        try {
            db.execSQL(alertAccRepSql.toString());
            db.execSQL(createAccRepSql());
            db.execSQL(insertAccRepSql.toString());
            db.execSQL(deleteAccRepSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 方法1：检查某表列是否存在
     *
     * @param tableName  表名
     * @param columnName 列名
     */
    private boolean checkColumnNotExist(SQLiteDatabase db, String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;
        try {
            //查询一行
            cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0"
                    , null);
            result = cursor != null && cursor.getColumnIndex(columnName) != -1;
        } catch (Exception e) {
            Log.e("tag", "checkColumnExists1..." + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return !result;
    }
}
